package com.example.Dao;

import com.example.pojo.News;
import com.example.utils.ConnUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDao {
    //统计总行
    public int getTotal(){
        int flag=0;
        Connection conn=ConnUtils.getConn();
        String sql="select count(*) from news";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            ResultSet rs= pst.executeQuery();
            if(rs.next()){
                flag=rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return flag;
    }
    //看新闻详情
    public News getNewsInfo(int newsId){
        News n=null;
        Connection conn=ConnUtils.getConn();
        String sql="select * from news where newsId=?";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,newsId);
            ResultSet rs= pst.executeQuery();
            if(rs.next()){
                n=new News(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10),
                        rs.getInt(11));
            }
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return n;
    }
    //根据播放量降序获取新闻列表
    public List<News> getNewsByViewCountDesc(){
        List<News> list=new ArrayList<>();
        Connection conn=ConnUtils.getConn();
        //按newsViews降序排列，限制显示10条
        String sql="select * from news where newsState=1 order by newsViews desc limit 10";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            ResultSet rs= pst.executeQuery();
            while(rs.next()){
                News n=new News(
                rs.getInt(1),
                        rs.getString(2),
                rs.getString(3),
                rs.getString(4),
                rs.getString(5),
                rs.getString(6),
                rs.getString(7),
                rs.getInt(8),
                rs.getInt(9),
                rs.getInt(10),
                rs.getInt(11));
                list.add(n);
            }
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return list;
    }
    //更新新闻播放量（阅读量）
    public int updateNewsViews(int newsId){
        int flag=0;
        Connection conn=ConnUtils.getConn();
        String sql="update news set newsViews=newsViews+1 where newsId=?";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            pst.setInt(1,newsId);
            flag= pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return flag;
    }

    //管理员角度
    public static List<News> getAllNews(int currentPage, int pageSize){
        List<News> list =new ArrayList<>();
        Connection conn=ConnUtils.getConn();
        String sql="select * from news limit ?,?";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,(currentPage-1)*pageSize);
            pst.setInt(2,pageSize);
            java.sql.ResultSet rs=pst.executeQuery();
            while(rs.next()){
                News n=new News(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10),
                        rs.getInt(11));
                list.add(n);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return list;
    }

    //添加新闻
    public int addNews(News news){
        int flag=0;
        //连接数据库
        Connection conn= ConnUtils.getConn();
        //编写SQL语句
        String sql="insert into news(newsType,newsTitle,newsContent,newsImg,publishDate,newsAuthor,newsTop,newsState)"+
                "values(?,?,?,?,SYSDATE(),?,?,?)";
        //PreparedStatement
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            pst.setString(1, news.getNewsType());
            pst.setString(2, news.getNewsTitle());
            pst.setString(3, news.getNewsContent());
            pst.setString(4, news.getNewsImg());
            pst.setString(5, news.getNewsAuthor());
            pst.setInt(6, news.getNewsTop());
            pst.setInt(7,news.getNewsState());
            flag=pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        //执行
        return flag;
    }

    //删除新闻
    public int deleteNewsById(int newsId){
        int flag=0;
        Connection conn=ConnUtils.getConn();
        String sql="delete from News where newsId=?";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            pst.setInt(1, newsId);
            flag = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return flag;
    }

    //批量删除
    public  int deleteBatchNews(List<Integer> ids){
        int flag=0;
        Connection conn=ConnUtils.getConn();
        //自动提交关闭(保持一致性----事务处理)
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        StringBuffer sbuffer=new StringBuffer();//可变长的字符串
        for(int i=0;i<ids.size();i++){
            sbuffer.append("?");  //0?,1?,2?
            if(i<ids.size()-1){
                sbuffer.append(",");
            }
        }
        String sql="delete from news where newsId in ("+sbuffer.toString()+")";
        System.out.println(sql);
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            for(int i=0;i< ids.size();i++){
                pst.setInt(i+1,ids.get(i));
            }
            flag= pst.executeUpdate();

//           int rs[]= pst.executeBatch();
//            for(int i=0;i<rs.length;i++){
//                flag+=rs[i];
//            }
            if(flag>0){
                //手动提交
                conn.commit();
            }
        } catch (SQLException e) {
            //回滚
            try {
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }
        return flag;
    }

    //查询新闻类型
    public List<News> getNewsByType(String type){//查询多条数据，一定返回List
        List<News> list=new ArrayList<>();
        Connection conn=ConnUtils.getConn();
        String sql="select * from news where newsType=?";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            //执行
            pst.setString(1,type);
            ResultSet rs= pst.executeQuery();
            //封装
            while(rs.next()){ //rs.next()----->News对象------>List
                News n=new News(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(7),
                        rs.getString(6),
                        rs.getInt(8),
                rs.getInt(8),
                rs.getInt(8),
                rs.getInt(8));
                //把n对象添加List集合中
                list.add(n);
            }
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        return list;
    }

    //编辑新闻
    public int EditNews(News news){
        int flag=0;
        //连接数据库
        Connection conn= ConnUtils.getConn();
        //编写SQL语句
        String sql="update news set newsType=?,newsTitle=?, newsContent=?, newsImg=?, newsAuthor=?,newsTop=?, newsState=? where newsId=?;";
        //PreparedStatement
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            pst.setString(1, news.getNewsType());
            pst.setString(2, news.getNewsTitle());
            pst.setString(3, news.getNewsContent());
            pst.setString(4, news.getNewsImg());
            pst.setString(5, news.getNewsAuthor());
            pst.setInt(6,news.getNewsState());
            pst.setInt(7,news.getNewsState());
            pst.setInt(8,news.getNewsId());
            flag=pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            ConnUtils.closeConnection(conn);
        }
        //执行
        return flag;
    }

    public List<News> getCarouselNews() {
        List<News> list = new ArrayList<>();
        Connection conn = ConnUtils.getConn();
        // 逻辑：查询“置顶”的新闻（newsTop=1），最多显示4条（可自定义数量）
        String sql = "select * from news where newsTop=1 limit 4";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                News n = new News(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getInt(10),
                        rs.getInt(11)
                );
                list.add(n);
            }
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            ConnUtils.closeConnection(conn);
        }
        return list;
    }
    // 在 NewsDao 类中添加点赞方法
    public int incrementLikes(int newsId) {
        int flag = 0;
        Connection conn = ConnUtils.getConn();
        String sql = "UPDATE news SET newsLikes = COALESCE(newsLikes, 0) + 1 WHERE newsId = ?";

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, newsId);
            flag = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            ConnUtils.closeConnection(conn);
        }
        return flag;
    }
    public int decrementLikes(int newsId) {
        int flag = 0;
        Connection conn = ConnUtils.getConn();
        String sql = "UPDATE news SET newsLikes = GREATEST(COALESCE(newsLikes, 0) - 1, 0) WHERE newsId = ?";

        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, newsId);
            flag = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            ConnUtils.closeConnection(conn);
        }
        return flag;
    }
}
